An medium-sized coffee shop franchise - our client - is looking for three suited places in London to open new shops over there. The four new shops - three "standards" plus one flagship - should be
a. well spread over Greater London Area.\ b. in zones with a low concentration of competitor shops (competitor = franchise with a number of shops between 3 and 6 in whole Greater London Area).\ c. in zones with a proved business resilience (i.e. high business survival rate).\ d. in zones with good pop density.\ e. the flagship should be placed in a INNER LONDON borough.\ f. the other three shops can be placed each of them in one of the three clusters assessed in OUTER Areas.
The client commissioned to our company a detailed analysis to find out four suited zones to open his shops by fulfilling the requirements listed in the Background.
Steps:\ I. The Greater London boroughs will be analyzed and clusterized by taking into account their geographical coordinates: particular focus will be placed on Business Survival Rate, Population Density and Competitors Shop Density.\ II. An appeal index by taking into account point b., c. and d. will be created, the index will be created by applying this weights to the major indexes defined:
| FACTORS | WEIGHTS |
|---|---|
| CoffeeShop Density per Ha | 45% |
| Business Survival Index | 25% |
| Population Density | 30% |
While Business Survival Rate and Population Density can be easily retrieved in the first link, Competitors Shop Density per borough will be assessed by using Foursquare to search for coffee shop in a 3.5 km radius from boroughs center.
Roughly, in the first step the London boroughs will be divided in three clusters while in the second step we will assess the three major indexes per each borough to support the decision where shops should be placed in: the decision will be addressed to the evaluation of an APPEAL INDEX calculated as weighted average of the three major indexes.
The APPEAL INDEX is meant as "the higher, the better": **the best performing borough per each cluster will be the winner**.
!pip install lxml
!pip install geopy
!pip install folium
!pip install geopy
!pip install pywaffle
!pip install OSMPythonTools
import numpy as np
import scipy as sp
import pandas as pd
from lxml import etree
import folium as fo
#from geopy.geocoders import Nominatim
import requests as rq
from sklearn.cluster import KMeans
import matplotlib as mpl
from matplotlib import cm
import seaborn as sns
from pywaffle import Waffle
from OSMPythonTools.nominatim import Nominatim
pd.set_option("precision", 5)
%matplotlib inline
Requirement already satisfied: lxml in g:\python\python39\lib\site-packages (4.6.2) Requirement already satisfied: geopy in g:\python\python39\lib\site-packages (2.1.0) Requirement already satisfied: geographiclib<2,>=1.49 in g:\python\python39\lib\site-packages (from geopy) (1.50) Requirement already satisfied: folium in g:\python\python39\lib\site-packages (0.12.1) Requirement already satisfied: numpy in g:\python\python39\lib\site-packages (from folium) (1.19.5) Requirement already satisfied: jinja2>=2.9 in g:\python\python39\lib\site-packages (from folium) (2.11.2) Requirement already satisfied: branca>=0.3.0 in g:\python\python39\lib\site-packages (from folium) (0.4.2) Requirement already satisfied: requests in g:\python\python39\lib\site-packages (from folium) (2.25.1) Requirement already satisfied: MarkupSafe>=0.23 in g:\python\python39\lib\site-packages (from jinja2>=2.9->folium) (1.1.1) Requirement already satisfied: urllib3<1.27,>=1.21.1 in g:\python\python39\lib\site-packages (from requests->folium) (1.26.2) Requirement already satisfied: certifi>=2017.4.17 in g:\python\python39\lib\site-packages (from requests->folium) (2020.12.5) Requirement already satisfied: idna<3,>=2.5 in g:\python\python39\lib\site-packages (from requests->folium) (2.10) Requirement already satisfied: chardet<5,>=3.0.2 in g:\python\python39\lib\site-packages (from requests->folium) (4.0.0) Requirement already satisfied: geopy in g:\python\python39\lib\site-packages (2.1.0) Requirement already satisfied: geographiclib<2,>=1.49 in g:\python\python39\lib\site-packages (from geopy) (1.50) Requirement already satisfied: pywaffle in g:\python\python39\lib\site-packages (0.6.1) Requirement already satisfied: matplotlib in g:\python\python39\lib\site-packages (from pywaffle) (3.3.3) Requirement already satisfied: numpy>=1.15 in g:\python\python39\lib\site-packages (from matplotlib->pywaffle) (1.19.5) Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in g:\python\python39\lib\site-packages (from matplotlib->pywaffle) (2.4.7) Requirement already satisfied: pillow>=6.2.0 in g:\python\python39\lib\site-packages (from matplotlib->pywaffle) (8.1.0) Requirement already satisfied: cycler>=0.10 in g:\python\python39\lib\site-packages (from matplotlib->pywaffle) (0.10.0) Requirement already satisfied: kiwisolver>=1.0.1 in g:\python\python39\lib\site-packages (from matplotlib->pywaffle) (1.3.1) Requirement already satisfied: python-dateutil>=2.1 in g:\python\python39\lib\site-packages (from matplotlib->pywaffle) (2.8.1) Requirement already satisfied: six in g:\python\python39\lib\site-packages (from cycler>=0.10->matplotlib->pywaffle) (1.15.0) Requirement already satisfied: OSMPythonTools in g:\python\python39\lib\site-packages (0.3.0) Requirement already satisfied: numpy in g:\python\python39\lib\site-packages (from OSMPythonTools) (1.19.5) Requirement already satisfied: pandas in g:\python\python39\lib\site-packages (from OSMPythonTools) (1.2.1) Requirement already satisfied: pytest-sugar in g:\python\python39\lib\site-packages (from OSMPythonTools) (0.9.4) Requirement already satisfied: geojson in g:\python\python39\lib\site-packages (from OSMPythonTools) (2.5.0) Requirement already satisfied: xarray in g:\python\python39\lib\site-packages (from OSMPythonTools) (0.16.2) Requirement already satisfied: ujson in g:\python\python39\lib\site-packages (from OSMPythonTools) (4.0.2) Requirement already satisfied: beautifulsoup4 in g:\python\python39\lib\site-packages (from OSMPythonTools) (4.9.3) Requirement already satisfied: pytest in g:\python\python39\lib\site-packages (from OSMPythonTools) (6.2.2) Requirement already satisfied: matplotlib in g:\python\python39\lib\site-packages (from OSMPythonTools) (3.3.3) Requirement already satisfied: lxml in g:\python\python39\lib\site-packages (from OSMPythonTools) (4.6.2) Requirement already satisfied: soupsieve>1.2 in g:\python\python39\lib\site-packages (from beautifulsoup4->OSMPythonTools) (2.2) Requirement already satisfied: pillow>=6.2.0 in g:\python\python39\lib\site-packages (from matplotlib->OSMPythonTools) (8.1.0) Requirement already satisfied: kiwisolver>=1.0.1 in g:\python\python39\lib\site-packages (from matplotlib->OSMPythonTools) (1.3.1) Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in g:\python\python39\lib\site-packages (from matplotlib->OSMPythonTools) (2.4.7) Requirement already satisfied: python-dateutil>=2.1 in g:\python\python39\lib\site-packages (from matplotlib->OSMPythonTools) (2.8.1) Requirement already satisfied: cycler>=0.10 in g:\python\python39\lib\site-packages (from matplotlib->OSMPythonTools) (0.10.0) Requirement already satisfied: six in g:\python\python39\lib\site-packages (from cycler>=0.10->matplotlib->OSMPythonTools) (1.15.0) Requirement already satisfied: pytz>=2017.3 in g:\python\python39\lib\site-packages (from pandas->OSMPythonTools) (2020.5) Requirement already satisfied: attrs>=19.2.0 in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (20.3.0) Requirement already satisfied: py>=1.8.2 in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (1.10.0) Requirement already satisfied: iniconfig in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (1.1.1) Requirement already satisfied: toml in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (0.10.2) Requirement already satisfied: packaging in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (20.8) Requirement already satisfied: atomicwrites>=1.0 in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (1.4.0) Requirement already satisfied: colorama in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (0.4.4) Requirement already satisfied: pluggy<1.0.0a1,>=0.12 in g:\python\python39\lib\site-packages (from pytest->OSMPythonTools) (0.13.1) Requirement already satisfied: termcolor>=1.1.0 in g:\python\python39\lib\site-packages (from pytest-sugar->OSMPythonTools) (1.1.0) Requirement already satisfied: setuptools>=38.4 in g:\python\python39\lib\site-packages (from xarray->OSMPythonTools) (49.2.1)
Statistical Data is loaded from Greater London Authority site: this data will provide info regarding
I. Boroughs code\ II. Borough Short Name\ III. Inland Area in Ha\ IV. Population Density per HA\ V. Average Age (2017)\ VI. Proportion of Population of Working Age (2015)\ VII. Two-Year Business Survival Rate (started in 2013)
it is worth to notice that for this analysis points V. and VI. will be neglected (included for further analysis).
# Source file saved locally
LondonDatasetPath = 'C:/Users/Marco/Desktop/london-borough-profiles.csv'
# Import selected columns
RequiredCols = ['Code', 'Area_name','Inner/_Outer_London','Inland_Area_(Hectares)','Population_density_(per_hectare)_2017','Average_Age,_2017', \
'Proportion_of_population_of_working-age,_2015', 'Two-year_business_survival_rates_(started_in_2013)']
# Read Dataset
LondonDataset = pd.read_csv(LondonDatasetPath, usecols=RequiredCols)
# Some Rows are general stats (e.g. whole England\United Kingdom stats), they have to be dropped out
LondonDataset.dropna(how='any',subset=['Inner/_Outer_London'],inplace=True)
# Print total number of boroughs in London
print('\n{} boroughs found in Greater London Area.\n'.format(LondonDataset.shape[0]))
# Substitute comma with poin in Inland_Area column and cast to float
LondonDataset['Inland_Area_(Hectares)'] = LondonDataset['Inland_Area_(Hectares)'].str.replace(',','').astype('float64', copy=True, errors='raise')
# Cast Population_density_(per_hectare)_2017 to float
LondonDataset['Population_density_(per_hectare)_2017'] = LondonDataset['Population_density_(per_hectare)_2017'].astype('float64', copy=True, errors='raise')
# Show Dataset
LondonDataset
33 boroughs found in Greater London Area.
| Code | Area_name | Inner/_Outer_London | Inland_Area_(Hectares) | Population_density_(per_hectare)_2017 | Average_Age,_2017 | Proportion_of_population_of_working-age,_2015 | Two-year_business_survival_rates_(started_in_2013) | |
|---|---|---|---|---|---|---|---|---|
| 0 | E09000001 | City of London | Inner London | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 |
| 1 | E09000002 | Barking and Dagenham | Outer London | 3611.0 | 57.9 | 32.9 | 63.1 | 73.0 |
| 2 | E09000003 | Barnet | Outer London | 8675.0 | 44.9 | 37.3 | 64.9 | 73.8 |
| 3 | E09000004 | Bexley | Outer London | 6058.0 | 40.3 | 39.0 | 62.9 | 73.5 |
| 4 | E09000005 | Brent | Outer London | 4323.0 | 76.8 | 35.6 | 67.8 | 74.4 |
| 5 | E09000006 | Bromley | Outer London | 15013.0 | 21.8 | 40.2 | 62.6 | 78.6 |
| 6 | E09000007 | Camden | Inner London | 2179.0 | 111.3 | 36.4 | 71.0 | 73.6 |
| 7 | E09000008 | Croydon | Outer London | 8650.0 | 44.7 | 37.0 | 64.9 | 75.3 |
| 8 | E09000009 | Ealing | Outer London | 5554.0 | 63.3 | 36.2 | 66.8 | 75.8 |
| 9 | E09000010 | Enfield | Outer London | 8083.0 | 41.2 | 36.3 | 64.4 | 74.2 |
| 10 | E09000011 | Greenwich | Outer London | 4733.0 | 59.2 | 35.0 | 67.7 | 72.7 |
| 11 | E09000012 | Hackney | Inner London | 1905.0 | 144.0 | 33.1 | 72.1 | 76.8 |
| 12 | E09000013 | Hammersmith and Fulham | Inner London | 1640.0 | 113.0 | 35.7 | 72.3 | 73.4 |
| 13 | E09000014 | Haringey | Inner London | 2960.0 | 93.9 | 35.1 | 70.7 | 74.4 |
| 14 | E09000015 | Harrow | Outer London | 5046.0 | 50.0 | 38.3 | 64.5 | 76.5 |
| 15 | E09000016 | Havering | Outer London | 11235.0 | 22.6 | 40.3 | 62.3 | 75.3 |
| 16 | E09000017 | Hillingdon | Outer London | 11570.0 | 26.0 | 36.4 | 65.6 | 75.0 |
| 17 | E09000018 | Hounslow | Outer London | 5598.0 | 49.0 | 35.8 | 67.6 | 76.2 |
| 18 | E09000019 | Islington | Inner London | 1486.0 | 155.6 | 34.8 | 75.3 | 72.5 |
| 19 | E09000020 | Kensington and Chelsea | Inner London | 1212.0 | 131.1 | 39.3 | 69.3 | 74.5 |
| 20 | E09000021 | Kingston upon Thames | Outer London | 3726.0 | 47.1 | 37.1 | 67.2 | 76.8 |
| 21 | E09000022 | Lambeth | Inner London | 2681.0 | 122.7 | 34.5 | 74.6 | 63.8 |
| 22 | E09000023 | Lewisham | Inner London | 3515.0 | 86.3 | 35.0 | 70.1 | 73.4 |
| 23 | E09000024 | Merton | Outer London | 3762.0 | 55.3 | 36.7 | 67.2 | 78.4 |
| 24 | E09000025 | Newham | Inner London | 3620.0 | 94.7 | 32.1 | 70.2 | 70.0 |
| 25 | E09000026 | Redbridge | Outer London | 5642.0 | 53.9 | 35.8 | 65.0 | 74.7 |
| 26 | E09000027 | Richmond upon Thames | Outer London | 5741.0 | 34.4 | 38.8 | 64.5 | 78.8 |
| 27 | E09000028 | Southwark | Inner London | 2886.0 | 108.9 | 34.4 | 73.5 | 73.4 |
| 28 | E09000029 | Sutton | Outer London | 4385.0 | 46.2 | 38.9 | 64.3 | 76.0 |
| 29 | E09000030 | Tower Hamlets | Inner London | 1978.0 | 153.7 | 31.4 | 73.9 | 69.7 |
| 30 | E09000031 | Waltham Forest | Outer London | 3881.0 | 71.2 | 35.1 | 67.9 | 71.0 |
| 31 | E09000032 | Wandsworth | Inner London | 3426.0 | 93.7 | 35.0 | 72.8 | 75.8 |
| 32 | E09000033 | Westminster | Inner London | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 |
Let's integrate data coming from Greater London Authority with data collected by using OpenStreetMap: some extra columns - featuring bourough's center Latitude, Longitude and Extended Name - will added up. Also each borough boundaries will be saved for future usage.
GeoDataDict = {'Area_name': [],'Extended_name': [],'Lat': [],'Lon': [],'Boundary': []}
for idx, Area in LondonDataset.iterrows():
print('Checking "{}"\n'.format(Area['Area_name']))
nominatim = Nominatim()
if (Area['Area_name'] in ['City of London','Westminster']):
AreaInfo = nominatim.query('{}, Greater London, England, United Kingdom'.format(Area['Area_name'])).toJSON()
elif (Area['Area_name'] in ['Kensington and Chelsea', 'Kingston upon Thames','Greenwich']):
AreaInfo = nominatim.query('Royal Borough of {}, Greater London, England, United Kingdom'.format(Area['Area_name'])).toJSON()
else:
AreaInfo = nominatim.query('London Borough of {}, Greater London, England, United Kingdom'.format(Area['Area_name'])).toJSON()
AreaInfo = [a for a in AreaInfo if a['osm_type']=='relation' and a['type']=='administrative']
print('\t{}: {}\n\tLat={:6.3f}, Lon={:6.3f}\n'.format(idx,AreaInfo[0]['display_name'],float(AreaInfo[0]['lat']),float(AreaInfo[0]['lon'])))
GeoDataDict['Area_name'].append(Area['Area_name'])
GeoDataDict['Extended_name'].append(AreaInfo[0]['display_name'])
GeoDataDict['Lat'].append(float(AreaInfo[0]['lat']))
GeoDataDict['Lon'].append(float(AreaInfo[0]['lon']))
req = rq.get('http://polygons.openstreetmap.fr/get_geojson.py?id={}¶ms=0'.format(AreaInfo[0]['osm_id']))
data = req.json()
GeoDataDict['Boundary'].append(data)
GeoData = pd.DataFrame(data=GeoDataDict)
Checking "City of London" 0: City of London, Greater London, England, United Kingdom Lat=51.516, Lon=-0.092 Checking "Barking and Dagenham" 1: London Borough of Barking and Dagenham, London, Greater London, England, United Kingdom Lat=51.554, Lon= 0.151 Checking "Barnet" 2: London Borough of Barnet, London, Greater London, England, United Kingdom Lat=51.613, Lon=-0.211 Checking "Bexley" 3: London Borough of Bexley, London, Greater London, England, United Kingdom Lat=51.462, Lon= 0.146 Checking "Brent" 4: London Borough of Brent, London, Greater London, England, United Kingdom Lat=51.564, Lon=-0.276 Checking "Bromley" 5: London Borough of Bromley, London, Greater London, England, United Kingdom Lat=51.367, Lon= 0.062 Checking "Camden" 6: London Borough of Camden, London, Greater London, England, United Kingdom Lat=51.543, Lon=-0.163 Checking "Croydon" 7: London Borough of Croydon, London, Greater London, England, United Kingdom Lat=51.355, Lon=-0.064 Checking "Ealing" 8: London Borough of Ealing, London, Greater London, England, United Kingdom Lat=51.525, Lon=-0.314 Checking "Enfield" 9: London Borough of Enfield, London, Greater London, England, United Kingdom Lat=51.649, Lon=-0.081 Checking "Greenwich" 10: Royal Borough of Greenwich, London, Greater London, England, United Kingdom Lat=51.469, Lon= 0.049 Checking "Hackney" 11: London Borough of Hackney, London, Greater London, England, United Kingdom Lat=51.549, Lon=-0.048 Checking "Hammersmith and Fulham" 12: London Borough of Hammersmith and Fulham, London, Greater London, England, United Kingdom Lat=51.498, Lon=-0.228 Checking "Haringey" 13: London Borough of Haringey, London, Greater London, England, United Kingdom Lat=51.588, Lon=-0.105 Checking "Harrow" 14: London Borough of Harrow, London, Greater London, England, United Kingdom Lat=51.597, Lon=-0.337 Checking "Havering" 15: London Borough of Havering, London, Greater London, England, United Kingdom Lat=51.558, Lon= 0.250 Checking "Hillingdon" 16: London Borough of Hillingdon, London, Greater London, England, United Kingdom Lat=51.543, Lon=-0.448 Checking "Hounslow" 17: London Borough of Hounslow, London, Greater London, England, United Kingdom Lat=51.462, Lon=-0.380 Checking "Islington" 18: London Borough of Islington, London, Greater London, England, United Kingdom Lat=51.547, Lon=-0.102 Checking "Kensington and Chelsea" 19: Royal Borough of Kensington and Chelsea, London, Greater London, England, United Kingdom Lat=51.504, Lon=-0.201 Checking "Kingston upon Thames" 20: Royal Borough of Kingston upon Thames, London, Greater London, England, United Kingdom Lat=51.382, Lon=-0.277 Checking "Lambeth" 21: London Borough of Lambeth, London, Greater London, England, United Kingdom Lat=51.460, Lon=-0.121 Checking "Lewisham" 22: London Borough of Lewisham, London, Greater London, England, United Kingdom Lat=51.453, Lon=-0.013 Checking "Merton" 23: London Borough of Merton, London, Greater London, England, United Kingdom Lat=51.411, Lon=-0.188 Checking "Newham" 24: London Borough of Newham, London, Greater London, England, United Kingdom Lat=51.530, Lon= 0.029 Checking "Redbridge" 25: London Borough of Redbridge, London, Greater London, England, United Kingdom Lat=51.586, Lon= 0.070 Checking "Richmond upon Thames" 26: London Borough of Richmond upon Thames, London, Greater London, England, United Kingdom Lat=51.441, Lon=-0.308 Checking "Southwark" 27: London Borough of Southwark, London, Greater London, England, United Kingdom Lat=51.465, Lon=-0.069 Checking "Sutton" 28: London Borough of Sutton, London, Greater London, England, United Kingdom Lat=51.357, Lon=-0.174 Checking "Tower Hamlets" 29: London Borough of Tower Hamlets, London, Greater London, England, United Kingdom Lat=51.515, Lon=-0.035 Checking "Waltham Forest" 30: London Borough of Waltham Forest, London, Greater London, England, United Kingdom Lat=51.598, Lon=-0.018 Checking "Wandsworth" 31: London Borough of Wandsworth, London, Greater London, England, United Kingdom Lat=51.452, Lon=-0.200 Checking "Westminster" 32: City of Westminster, London, Greater London, England, United Kingdom Lat=51.497, Lon=-0.137
Let's show the boroughs boundaries in a OpenStreetMap view: each circle represents the point used to assess the borough Latitude and Longitude.
# Show a map of each borough boundaries
London = nominatim.query('London, Greater London, England, United Kingdom').toJSON()
print('\nCenter map on: {}\n'.format(London[0]['display_name']))
map_london = fo.Map(location=[float(London[0]['lat']), float(London[0]['lon'])])
map_london.fit_bounds(bounds=[
[float(London[0]['boundingbox'][0]), float(London[0]['boundingbox'][2])],
[float(London[0]['boundingbox'][1]), float(London[0]['boundingbox'][3])],
])
for B in GeoData['Boundary']:
borough_map = fo.Choropleth(
geo_data=B,
name='choropleth',
fill_color='Yellow',
fill_opacity=0.15,
line_weight=2.0,
line_opacity=0.80
).add_to(map_london)
for Num, Val in GeoData.iterrows():
fo.CircleMarker([Val['Lat'],Val['Lon']], radius=3, tooltip=fo.Tooltip(Val['Extended_name']), fill=True, color='black').add_to(map_london)
map_london
Center map on: London, Greater London, England, United Kingdom
Let's start to aggregate the OUTER LONDON boroughs in three clusters: each of them will host a shop.\ The cluster creation criterium is based on Geographical Positioning and it will be base on k-Means Algorithm.\ The fourth cluster is made up by INNER LONDON boroughs and it will host the flagship store.
# Create ExtendedLondonDataset
ExtendedLondonDataset = LondonDataset.merge(GeoData)
# Prepare Data for clustering: the Areas will be clustered basing on geo coordinates
IdxOut = ExtendedLondonDataset['Inner/_Outer_London'] == 'Outer London'
IdxIn = ExtendedLondonDataset['Inner/_Outer_London'] == 'Inner London'
X = ExtendedLondonDataset[['Lat','Lon']].loc[IdxOut]
# Create k-Means Obj
LondonClusters = KMeans(n_clusters=3, n_init=38, max_iter=2000)
# Fit k-Means Obj to
ClusterData = LondonClusters.fit_predict(X)
# Add Extra Colums to feature the clusters: 0 is Inner London, other numbers are labelling the Outer London Clusters
ExtendedLondonDataset['ClusterArea'] = ExtendedLondonDataset.shape[0]*[-1]
ExtendedLondonDataset.loc[IdxOut, ['ClusterArea']] = ClusterData+1
ExtendedLondonDataset.loc[IdxIn, ['ClusterArea']] = 0
# Define Cluster Centers
ClusterCenters = LondonClusters.cluster_centers_
The cluster no of each borough will be integrated in the dataset, the columns of this dataset will be rearranged to make easier the readability.\ An ad-hoc GeoJson file will make possible to easily integrate and use Statistical Data on OpenStreetMap map.
# Organize Column Names
columns = ExtendedLondonDataset.columns.to_list()
columns = columns[0:3]+columns[-5:-2]+columns[-1:]+columns[3:8]
ExtendedLondonDataset = ExtendedLondonDataset[columns]
ExtendedLondonDataset.head()
| Code | Area_name | Inner/_Outer_London | Extended_name | Lat | Lon | ClusterArea | Inland_Area_(Hectares) | Population_density_(per_hectare)_2017 | Average_Age,_2017 | Proportion_of_population_of_working-age,_2015 | Two-year_business_survival_rates_(started_in_2013) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 |
| 1 | E09000002 | Barking and Dagenham | Outer London | London Borough of Barking and Dagenham, London... | 51.55412 | 0.15050 | 2 | 3611.0 | 57.9 | 32.9 | 63.1 | 73.0 |
| 2 | E09000003 | Barnet | Outer London | London Borough of Barnet, London, Greater Lond... | 51.61252 | -0.21144 | 1 | 8675.0 | 44.9 | 37.3 | 64.9 | 73.8 |
| 3 | E09000004 | Bexley | Outer London | London Borough of Bexley, London, Greater Lond... | 51.46197 | 0.14570 | 2 | 6058.0 | 40.3 | 39.0 | 62.9 | 73.5 |
| 4 | E09000005 | Brent | Outer London | London Borough of Brent, London, Greater Londo... | 51.56383 | -0.27576 | 1 | 4323.0 | 76.8 | 35.6 | 67.8 | 74.4 |
LondonBoundaries = {'type': 'FeatureCollection','features': []}
for code, area_name, london_area, boundary, cluster_area in zip(ExtendedLondonDataset['Code'], ExtendedLondonDataset['Area_name'], ExtendedLondonDataset['Inner/_Outer_London'], GeoData['Boundary'], ExtendedLondonDataset['ClusterArea']):
Val = {'type': 'Feature', 'id': code, 'properties': {'name': area_name, 'area': london_area, 'cluster': cluster_area},
'geometry': {'type': 'Polygon', 'coordinates': boundary['geometries'][0]['coordinates'][0]}}
LondonBoundaries["features"].append(Val)
London = nominatim.query('London, Greater London, England, United Kingdom').toJSON()
print('\nCenter map on: {}\n'.format(London[0]['display_name']))
map_london = fo.Map(location=[float(London[0]['lat']), float(London[0]['lon'])], tiles='OpenStreetMap')
map_london.fit_bounds(bounds=[
[float(London[0]['boundingbox'][0]), float(London[0]['boundingbox'][2])],
[float(London[0]['boundingbox'][1]), float(London[0]['boundingbox'][3])],
])
stfun1 = lambda x: {'fillColor': 'red' if x['properties']['area']=='Inner London' else 'blue', 'fillOpacity': 0.2, 'color': 'black', 'weight': 1}
borough_map = fo.features.GeoJson(LondonBoundaries, style_function=stfun1, name='London Boroughs', tooltip=fo.GeoJsonTooltip(fields=['name'], aliases=['Borough: ']), show=True).add_to(map_london)
def stfun2(feature):
dict_out = {'fillOpacity': 0.8, 'color': 'black', 'weight': 1}
if feature['properties']['cluster']==0:
dict_out['fillColor'] = 'red'
elif feature['properties']['cluster']==1:
dict_out['fillColor'] = 'blue'
elif feature['properties']['cluster']==2:
dict_out['fillColor'] = 'lightblue'
elif feature['properties']['cluster']==3:
dict_out['fillColor'] = 'cadetblue'
return dict_out
cluster_map = fo.features.GeoJson(LondonBoundaries, style_function=stfun2, name='Cluster Boroughs', tooltip=fo.GeoJsonTooltip(fields=['name', 'cluster'], aliases=['Borough: ', 'Cluster #']), show=False).add_to(map_london)
map_london
Center map on: London, Greater London, England, United Kingdom
Four areas have been isolated:
| Area | Boroughs No | Cluster No | Color |
|---|---|---|---|
| INNER LONDON | 14 | #0 | Red |
| OUTER LONDON/WEST AREA | 7 | #1 | Dark Blue |
| OUTER LONDON/NORTH AND EAST AREA | 8 | #2 | Light Blue |
| OUTER LONDON/SOUTH AREA | 4 | #3 | Cadet Blue |
1) the INNER LONDON will host the flagship shop (14 Boroughs, Cluster #0, Red) 2) One shop will be placed in the OUTER LONDON/WEST AREA (7 Boroughs, Cluster #2, Dark Blue) 3) One shop will be placed in the OUTER LONDON/NORTH AND EAST AREA (8 Boroughs, Cluster #2, Light Blue) 4) One shop will be placed in the OUTER LONDON/SOUTH AREA (4 Boroughs, Cluster #3, Cadet Blue)
Through the usage of Foursquare API, we will be able to count the number of coffee shops in each borough, the main criterium is a max distance of each borough center within 3.5 km.
CLIENT_ID = 'xxxxx' # your Foursquare ID
CLIENT_SECRET = 'xxxxx' # your Foursquare Secret
ACCESS_TOKEN = 'xxxxx' # your FourSquare Access Token
VERSION = '20180604'
LIMIT = 30
def getNearbyVenues(names, radius, queryshop):
venues_list=[]
for name in names:
near = '{}, London'.format(name)
# create the API request URL
url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&near={}&radius={}&limit={}&query={}'.format(
CLIENT_ID,
CLIENT_SECRET,
VERSION,
near,
radius,
LIMIT,
queryshop)
# make the GET request
results = rq.get(url).json()["response"]['groups'][0]['items']
# return only relevant information for each nearby venue
venues_list.append([(
name,
v['venue']['name'],
v['venue']['location']['lat'],
v['venue']['location']['lng'],
v['venue']['categories'][0]['name']) for v in results])
nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
nearby_venues.columns = ['Area_name',
'Venue',
'Venue Latitude',
'Venue Longitude',
'Venue Category']
return(nearby_venues)
london_venues = getNearbyVenues(names=ExtendedLondonDataset['Area_name'],
radius=3500,
queryshop='Coffee Shop')
print('\nA total of {} coffee shops found in the {} boroughs.\nA sample is shown below.\n'.format(london_venues.shape[0], LondonDataset.shape[0]))
london_venues
A total of 981 coffee shops found in the 33 boroughs. A sample is shown below.
| Area_name | Venue | Venue Latitude | Venue Longitude | Venue Category | |
|---|---|---|---|---|---|
| 0 | City of London | WA Cafe | 51.51101 | -0.12661 | Café |
| 1 | City of London | Monmouth Coffee Company | 51.51431 | -0.12682 | Coffee Shop |
| 2 | City of London | Coffee Island | 51.51245 | -0.12718 | Coffee Shop |
| 3 | City of London | Lundenwic | 51.51282 | -0.11834 | Coffee Shop |
| 4 | City of London | % Arabica | 51.51173 | -0.12405 | Coffee Shop |
| ... | ... | ... | ... | ... | ... |
| 976 | Westminster | Hagen | 51.50982 | -0.13781 | Coffee Shop |
| 977 | Westminster | The Roasting | 51.49136 | -0.13896 | Coffee Shop |
| 978 | Westminster | Prufrock Coffee | 51.51993 | -0.10945 | Coffee Shop |
| 979 | Westminster | L'ETO Caffè | 51.51434 | -0.13467 | Café |
| 980 | Westminster | Roasting Plant Coffee | 51.50626 | -0.08835 | Coffee Shop |
981 rows × 5 columns
Some useful info regarding coffee shops in London: in the chart we have a snapshot regarding the most important franchise per number of shops (only companies with more than 3 shops are shown here).\ Our client is interested in the market segment of competitors with a number of shop in Greater London Area between 3 and 7: this dataset and pertaining stats are shown as a text in the bottom of this section.
Stats = london_venues['Venue'].value_counts()
print('\nShow all franchise with more than 3 shops in Greater London Area.')
fig, ax = mpl.pyplot.subplots()
fig.set_size_inches(11.7, 8.27)
sns.set_theme(style="darkgrid")
sns.barplot(y=Stats[Stats>3].index, x=Stats[Stats>3].values, orient='h', ax=ax)
sns.set(font_scale=1.2)
ax.set_title('All franchise with more than 3 shops in Greater London Area')
print('\n')
print(Stats[Stats>3])
print('\n')
Show all franchise with more than 3 shops in Greater London Area. Costa Coffee 133 Starbucks 42 Caffè Nero 38 Wild Bean Cafe 8 Harris + Hoole 8 The Gentlemen Baristas 6 Monmouth Coffee Company 6 Patisserie Valerie 5 Alchemy 5 Redemption Roasters 4 EL&N 4 The Watch House 4 FCB Coffee 4 Black Sheep Coffee 4 Grounded 4 The Press Room 4 L'ETO Caffè 4 Name: Venue, dtype: int64
CoffeeShopsStats = Stats[Stats.isin(range(3,7))]
print('Total number of competitors shops in Greater London Area is {}.'.format(CoffeeShopsStats.sum()))
Total number of competitors shops in Greater London Area is 129.
print('The number of shops per each competitor:')
CoffeeShopsStats
The number of shops per each competitor:
The Gentlemen Baristas 6 Monmouth Coffee Company 6 Patisserie Valerie 5 Alchemy 5 Redemption Roasters 4 EL&N 4 The Watch House 4 FCB Coffee 4 Black Sheep Coffee 4 Grounded 4 The Press Room 4 L'ETO Caffè 4 Riverside Cafe 3 Hagen 3 Esters 3 Yellow Warbler 3 % Arabica 3 Roasting Plant Coffee 3 Watch House 3 Muffin Break 3 Coffee Republic 3 Prufrock Coffee 3 Lever & Bloom Coffee 3 Lundenwic 3 M&S Café 3 Formative 3 Rosslyn 3 Original Maids of Honour Tearoom 3 Iris & June 3 Mouse Tail Coffee Stories 3 Bake Street 3 Café Z Bar 3 WA Cafe 3 Sapori Café & Restaurant 3 Coffee Island 3 Trade Coffee 3 Sift Bakes & Brews 3 Name: Venue, dtype: int64
Boroughs stats are merged with Venue Stats to make easier the combined analysis required to set up the Appeal Index.\ A large dataset - featuring each venue stat paired with hosting borough stat - will be created.
ExtendedLondonDatasetwithVenues = ExtendedLondonDataset.join(london_venues.set_index('Area_name'), how='inner', on='Area_name')
Idx = [x in CoffeeShopsStats.index.to_list() for x in (ExtendedLondonDatasetwithVenues['Venue'].to_list())]
ExtendedLondonDatasetwithVenues = ExtendedLondonDatasetwithVenues.loc[Idx,:]
ExtendedLondonDatasetwithVenues.reset_index()
| index | Code | Area_name | Inner/_Outer_London | Extended_name | Lat | Lon | ClusterArea | Inland_Area_(Hectares) | Population_density_(per_hectare)_2017 | Average_Age,_2017 | Proportion_of_population_of_working-age,_2015 | Two-year_business_survival_rates_(started_in_2013) | Venue | Venue Latitude | Venue Longitude | Venue Category | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | WA Cafe | 51.51101 | -0.12661 | Café |
| 1 | 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | Monmouth Coffee Company | 51.51431 | -0.12682 | Coffee Shop |
| 2 | 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | Coffee Island | 51.51245 | -0.12718 | Coffee Shop |
| 3 | 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | Lundenwic | 51.51282 | -0.11834 | Coffee Shop |
| 4 | 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | % Arabica | 51.51173 | -0.12405 | Coffee Shop |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 124 | 32 | E09000033 | Westminster | Inner London | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 | The Gentlemen Baristas | 51.50531 | -0.09188 | Coffee Shop |
| 125 | 32 | E09000033 | Westminster | Inner London | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 | Hagen | 51.50982 | -0.13781 | Coffee Shop |
| 126 | 32 | E09000033 | Westminster | Inner London | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 | Prufrock Coffee | 51.51993 | -0.10945 | Coffee Shop |
| 127 | 32 | E09000033 | Westminster | Inner London | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 | L'ETO Caffè | 51.51434 | -0.13467 | Café |
| 128 | 32 | E09000033 | Westminster | Inner London | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 | Roasting Plant Coffee | 51.50626 | -0.08835 | Coffee Shop |
129 rows × 17 columns
A shrinked dataset will be crated started from the previous larger one: each borough will report stats plus the number of competitor shop in the borough.\ A multi-feature Choroplet map will show these stats.
CountShopsPerCluster = ExtendedLondonDatasetwithVenues.groupby(by=['Area_name'])['Venue'].count()
ZeroShopAreas = set(LondonDataset['Area_name'].to_list()).difference(set(CountShopsPerCluster.index.to_list()))
for v in ZeroShopAreas:
CountShopsPerCluster = CountShopsPerCluster.append(pd.Series([0],index=[v],name='Venue'),verify_integrity=True)
CountShopsPerCluster = CountShopsPerCluster.rename_axis('Area_name')
CountShopsPerCluster.sort_index(inplace=True)
ExtendedLondonDataset = ExtendedLondonDataset.merge(CountShopsPerCluster.reset_index(), how='inner')
ExtendedLondonDataset.rename(columns={'Venue': 'NoOfShops'}, inplace=True)
ExtendedLondonDataset
| Code | Area_name | Inner/_Outer_London | Extended_name | Lat | Lon | ClusterArea | Inland_Area_(Hectares) | Population_density_(per_hectare)_2017 | Average_Age,_2017 | Proportion_of_population_of_working-age,_2015 | Two-year_business_survival_rates_(started_in_2013) | NoOfShops | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | 16 |
| 1 | E09000002 | Barking and Dagenham | Outer London | London Borough of Barking and Dagenham, London... | 51.55412 | 0.15050 | 2 | 3611.0 | 57.9 | 32.9 | 63.1 | 73.0 | 1 |
| 2 | E09000003 | Barnet | Outer London | London Borough of Barnet, London, Greater Lond... | 51.61252 | -0.21144 | 1 | 8675.0 | 44.9 | 37.3 | 64.9 | 73.8 | 0 |
| 3 | E09000004 | Bexley | Outer London | London Borough of Bexley, London, Greater Lond... | 51.46197 | 0.14570 | 2 | 6058.0 | 40.3 | 39.0 | 62.9 | 73.5 | 0 |
| 4 | E09000005 | Brent | Outer London | London Borough of Brent, London, Greater Londo... | 51.56383 | -0.27576 | 1 | 4323.0 | 76.8 | 35.6 | 67.8 | 74.4 | 1 |
| 5 | E09000006 | Bromley | Outer London | London Borough of Bromley, London, Greater Lon... | 51.36686 | 0.06171 | 2 | 15013.0 | 21.8 | 40.2 | 62.6 | 78.6 | 2 |
| 6 | E09000007 | Camden | Inner London | London Borough of Camden, London, Greater Lond... | 51.54285 | -0.16251 | 0 | 2179.0 | 111.3 | 36.4 | 71.0 | 73.6 | 3 |
| 7 | E09000008 | Croydon | Outer London | London Borough of Croydon, London, Greater Lon... | 51.35506 | -0.06431 | 3 | 8650.0 | 44.7 | 37.0 | 64.9 | 75.3 | 1 |
| 8 | E09000009 | Ealing | Outer London | London Borough of Ealing, London, Greater Lond... | 51.52508 | -0.31429 | 1 | 5554.0 | 63.3 | 36.2 | 66.8 | 75.8 | 2 |
| 9 | E09000010 | Enfield | Outer London | London Borough of Enfield, London, Greater Lon... | 51.64874 | -0.08098 | 2 | 8083.0 | 41.2 | 36.3 | 64.4 | 74.2 | 2 |
| 10 | E09000011 | Greenwich | Outer London | Royal Borough of Greenwich, London, Greater Lo... | 51.46863 | 0.04884 | 2 | 4733.0 | 59.2 | 35.0 | 67.7 | 72.7 | 2 |
| 11 | E09000012 | Hackney | Inner London | London Borough of Hackney, London, Greater Lon... | 51.54888 | -0.04767 | 0 | 1905.0 | 144.0 | 33.1 | 72.1 | 76.8 | 5 |
| 12 | E09000013 | Hammersmith and Fulham | Inner London | London Borough of Hammersmith and Fulham, Lond... | 51.49831 | -0.22788 | 0 | 1640.0 | 113.0 | 35.7 | 72.3 | 73.4 | 3 |
| 13 | E09000014 | Haringey | Inner London | London Borough of Haringey, London, Greater Lo... | 51.58793 | -0.10541 | 0 | 2960.0 | 93.9 | 35.1 | 70.7 | 74.4 | 5 |
| 14 | E09000015 | Harrow | Outer London | London Borough of Harrow, London, Greater Lond... | 51.59683 | -0.33732 | 1 | 5046.0 | 50.0 | 38.3 | 64.5 | 76.5 | 0 |
| 15 | E09000016 | Havering | Outer London | London Borough of Havering, London, Greater Lo... | 51.55793 | 0.24981 | 2 | 11235.0 | 22.6 | 40.3 | 62.3 | 75.3 | 1 |
| 16 | E09000017 | Hillingdon | Outer London | London Borough of Hillingdon, London, Greater ... | 51.54252 | -0.44833 | 1 | 11570.0 | 26.0 | 36.4 | 65.6 | 75.0 | 0 |
| 17 | E09000018 | Hounslow | Outer London | London Borough of Hounslow, London, Greater Lo... | 51.46173 | -0.38013 | 1 | 5598.0 | 49.0 | 35.8 | 67.6 | 76.2 | 3 |
| 18 | E09000019 | Islington | Inner London | London Borough of Islington, London, Greater L... | 51.54703 | -0.10166 | 0 | 1486.0 | 155.6 | 34.8 | 75.3 | 72.5 | 6 |
| 19 | E09000020 | Kensington and Chelsea | Inner London | Royal Borough of Kensington and Chelsea, Londo... | 51.50380 | -0.20079 | 0 | 1212.0 | 131.1 | 39.3 | 69.3 | 74.5 | 6 |
| 20 | E09000021 | Kingston upon Thames | Outer London | Royal Borough of Kingston upon Thames, London,... | 51.38178 | -0.27699 | 3 | 3726.0 | 47.1 | 37.1 | 67.2 | 76.8 | 4 |
| 21 | E09000022 | Lambeth | Inner London | London Borough of Lambeth, London, Greater Lon... | 51.46040 | -0.12135 | 0 | 2681.0 | 122.7 | 34.5 | 74.6 | 63.8 | 15 |
| 22 | E09000023 | Lewisham | Inner London | London Borough of Lewisham, London, Greater Lo... | 51.45343 | -0.01251 | 0 | 3515.0 | 86.3 | 35.0 | 70.1 | 73.4 | 2 |
| 23 | E09000024 | Merton | Outer London | London Borough of Merton, London, Greater Lond... | 51.41087 | -0.18810 | 3 | 3762.0 | 55.3 | 36.7 | 67.2 | 78.4 | 0 |
| 24 | E09000025 | Newham | Inner London | London Borough of Newham, London, Greater Lond... | 51.53000 | 0.02932 | 0 | 3620.0 | 94.7 | 32.1 | 70.2 | 70.0 | 1 |
| 25 | E09000026 | Redbridge | Outer London | London Borough of Redbridge, London, Greater L... | 51.58637 | 0.06976 | 2 | 5642.0 | 53.9 | 35.8 | 65.0 | 74.7 | 1 |
| 26 | E09000027 | Richmond upon Thames | Outer London | London Borough of Richmond upon Thames, London... | 51.44055 | -0.30764 | 1 | 5741.0 | 34.4 | 38.8 | 64.5 | 78.8 | 5 |
| 27 | E09000028 | Southwark | Inner London | London Borough of Southwark, London, Greater L... | 51.46528 | -0.06904 | 0 | 2886.0 | 108.9 | 34.4 | 73.5 | 73.4 | 12 |
| 28 | E09000029 | Sutton | Outer London | London Borough of Sutton, London, Greater Lond... | 51.35746 | -0.17363 | 3 | 4385.0 | 46.2 | 38.9 | 64.3 | 76.0 | 2 |
| 29 | E09000030 | Tower Hamlets | Inner London | London Borough of Tower Hamlets, London, Great... | 51.51456 | -0.03501 | 0 | 1978.0 | 153.7 | 31.4 | 73.9 | 69.7 | 5 |
| 30 | E09000031 | Waltham Forest | Outer London | London Borough of Waltham Forest, London, Grea... | 51.59817 | -0.01784 | 2 | 3881.0 | 71.2 | 35.1 | 67.9 | 71.0 | 5 |
| 31 | E09000032 | Wandsworth | Inner London | London Borough of Wandsworth, London, Greater ... | 51.45190 | -0.19951 | 0 | 3426.0 | 93.7 | 35.0 | 72.8 | 75.8 | 0 |
| 32 | E09000033 | Westminster | Inner London | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 2149.0 | 112.7 | 37.7 | 72.3 | 68.8 | 18 |
London = nominatim.query('London, Greater London, England, United Kingdom').toJSON()
print('\nCenter map on: {}\n'.format(London[0]['display_name']))
map_london = fo.Map(location=[float(London[0]['lat']), float(London[0]['lon'])], tiles='OpenStreetMap')
map_london.fit_bounds(bounds=[
[float(London[0]['boundingbox'][0]), float(London[0]['boundingbox'][2])],
[float(London[0]['boundingbox'][1]), float(London[0]['boundingbox'][3])],
])
stfun1 = lambda x: {'fillColor': 'red' if x['properties']['area']=='Inner London' else 'blue', 'fillOpacity': 0.2, 'color': 'black', 'weight': 1}
borough_map = fo.features.GeoJson(LondonBoundaries, style_function=stfun1, name='London Boroughs', tooltip=fo.GeoJsonTooltip(fields=['name'], aliases=['Borough: ']), show=True).add_to(map_london)
def stfun2(feature):
dict_out = {'fillOpacity': 0.8, 'color': 'black', 'weight': 1}
if feature['properties']['cluster']==0:
dict_out['fillColor'] = 'red'
elif feature['properties']['cluster']==1:
dict_out['fillColor'] = 'blue'
elif feature['properties']['cluster']==2:
dict_out['fillColor'] = 'lightblue'
elif feature['properties']['cluster']==3:
dict_out['fillColor'] = 'cadetblue'
return dict_out
cluster_map = fo.features.GeoJson(LondonBoundaries, style_function=stfun2, name='Cluster Boroughs', tooltip=fo.GeoJsonTooltip(fields=['name', 'cluster'], aliases=['Borough: ', 'Cluster #']), show=False).add_to(map_london)
shop_no = fo.Choropleth(
geo_data=LondonBoundaries,
data=ExtendedLondonDataset,
columns=['Area_name', 'NoOfShops'],
key_on="feature.properties.name",
fill_color='YlGnBu',
fill_opacity=0.45,
line_weight=2.0,
line_opacity=0.80,
highlight=True,
name='No of competitors coffee shops',
legend_name='No of coffee shops (competitor) in the borough',
overlay=True,
show=False
).add_to(map_london)
fo.LayerControl(overlay=True).add_to(map_london)
map_london
Center map on: London, Greater London, England, United Kingdom
The Appeal Index is a weighted average of three different index:
to make the analysis homegeneous, each index is NORMALIZED in the range 0-1: 0 is a poor performance, 1 is the best one.
Shop Density per HA is an index meant as "the lower, the better", to make it consistent with the other two - made up with the logic "the higher, the better" - the complement to 1 is calculated.
# Calculate Density of shops per Hectar (normalized to 1)
ExtendedLondonDataset['Shop_Density_per_Hectar'] = ExtendedLondonDataset['NoOfShops']/ExtendedLondonDataset['Inland_Area_(Hectares)']
ExtendedLondonDataset['Shop_Density_per_Hectar'] = 1.0-ExtendedLondonDataset['Shop_Density_per_Hectar']/ExtendedLondonDataset['Shop_Density_per_Hectar'].max()
# Calculate Business Survival Rate (normalized to 1)
ExtendedLondonDataset['Business_Survival'] = ExtendedLondonDataset['Two-year_business_survival_rates_(started_in_2013)']/ExtendedLondonDataset['Two-year_business_survival_rates_(started_in_2013)'].max()
# Population (normalized to 1)
ExtendedLondonDataset['Population_Density'] = ExtendedLondonDataset['Population_density_(per_hectare)_2017']
ExtendedLondonDataset['Population_Density'] = ExtendedLondonDataset['Population_Density']/ExtendedLondonDataset['Population_Density'].max()
ExtendedLondonDataset.head()
| Code | Area_name | Inner/_Outer_London | Extended_name | Lat | Lon | ClusterArea | Inland_Area_(Hectares) | Population_density_(per_hectare)_2017 | Average_Age,_2017 | Proportion_of_population_of_working-age,_2015 | Two-year_business_survival_rates_(started_in_2013) | NoOfShops | Shop_Density_per_Hectar | Business_Survival | Population_Density | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | E09000001 | City of London | Inner London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 290.0 | 30.3 | 43.2 | 73.1 | 64.3 | 16 | 0.00000 | 0.81599 | 0.19473 |
| 1 | E09000002 | Barking and Dagenham | Outer London | London Borough of Barking and Dagenham, London... | 51.55412 | 0.15050 | 2 | 3611.0 | 57.9 | 32.9 | 63.1 | 73.0 | 1 | 0.99498 | 0.92640 | 0.37211 |
| 2 | E09000003 | Barnet | Outer London | London Borough of Barnet, London, Greater Lond... | 51.61252 | -0.21144 | 1 | 8675.0 | 44.9 | 37.3 | 64.9 | 73.8 | 0 | 1.00000 | 0.93655 | 0.28856 |
| 3 | E09000004 | Bexley | Outer London | London Borough of Bexley, London, Greater Lond... | 51.46197 | 0.14570 | 2 | 6058.0 | 40.3 | 39.0 | 62.9 | 73.5 | 0 | 1.00000 | 0.93274 | 0.25900 |
| 4 | E09000005 | Brent | Outer London | London Borough of Brent, London, Greater Londo... | 51.56383 | -0.27576 | 1 | 4323.0 | 76.8 | 35.6 | 67.8 | 74.4 | 1 | 0.99581 | 0.94416 | 0.49357 |
The three index are linearly combined with the factors to create the final Area Appeal Index that it will be used to make the final ranking.
# Define importance factors
Factors = 3*[0]
Factors[0] = 0.45 # Shop Density Factor
Factors[1] = 0.25 # Business Survival Factor
Factors[2] = 0.30 # Population Factor
# Area Appeal: it is an index spanning over 0 to 1 (the higher, teh better)
ExtendedLondonDataset['Area_Appeal_Index'] = Factors[0]*ExtendedLondonDataset['Shop_Density_per_Hectar']+Factors[1]*ExtendedLondonDataset['Business_Survival']+Factors[2]*ExtendedLondonDataset['Population_Density']
The borough appeal is shown here either as text and Choropleth Map.\ The Choropleth Map is multi-feature, the Appeal Index can be shown jointly with borough membership.
ReducedInfo = ['Area_name','Extended_name','Lat','Lon','ClusterArea','NoOfShops','Shop_Density_per_Hectar','Business_Survival','Population_Density','Area_Appeal_Index']
DataResume = ExtendedLondonDataset[ReducedInfo].sort_values(by='Area_Appeal_Index', ascending=False)
print('Greater London Area - Appeal Ranking')
DataResume
Greater London Area - Appeal Ranking
| Area_name | Extended_name | Lat | Lon | ClusterArea | NoOfShops | Shop_Density_per_Hectar | Business_Survival | Population_Density | Area_Appeal_Index | |
|---|---|---|---|---|---|---|---|---|---|---|
| 11 | Hackney | London Borough of Hackney, London, Greater Lon... | 51.54888 | -0.04767 | 0 | 5 | 0.95243 | 0.97462 | 0.92545 | 0.94988 |
| 18 | Islington | London Borough of Islington, London, Greater L... | 51.54703 | -0.10166 | 0 | 6 | 0.92682 | 0.92005 | 1.00000 | 0.94708 |
| 29 | Tower Hamlets | London Borough of Tower Hamlets, London, Great... | 51.51456 | -0.03501 | 0 | 5 | 0.95418 | 0.88452 | 0.98779 | 0.94685 |
| 19 | Kensington and Chelsea | Royal Borough of Kensington and Chelsea, Londo... | 51.50380 | -0.20079 | 0 | 6 | 0.91027 | 0.94543 | 0.84254 | 0.89874 |
| 6 | Camden | London Borough of Camden, London, Greater Lond... | 51.54285 | -0.16251 | 0 | 3 | 0.97505 | 0.93401 | 0.71530 | 0.88686 |
| 12 | Hammersmith and Fulham | London Borough of Hammersmith and Fulham, Lond... | 51.49831 | -0.22788 | 0 | 3 | 0.96684 | 0.93147 | 0.72622 | 0.88581 |
| 31 | Wandsworth | London Borough of Wandsworth, London, Greater ... | 51.45190 | -0.19951 | 0 | 0 | 1.00000 | 0.96193 | 0.60219 | 0.87114 |
| 27 | Southwark | London Borough of Southwark, London, Greater L... | 51.46528 | -0.06904 | 0 | 12 | 0.92464 | 0.93147 | 0.69987 | 0.85892 |
| 13 | Haringey | London Borough of Haringey, London, Greater Lo... | 51.58793 | -0.10541 | 0 | 5 | 0.96938 | 0.94416 | 0.60347 | 0.85330 |
| 24 | Newham | London Borough of Newham, London, Greater Lond... | 51.53000 | 0.02932 | 0 | 1 | 0.99499 | 0.88832 | 0.60861 | 0.85241 |
| 22 | Lewisham | London Borough of Lewisham, London, Greater Lo... | 51.45343 | -0.01251 | 0 | 2 | 0.98969 | 0.93147 | 0.55463 | 0.84462 |
| 21 | Lambeth | London Borough of Lambeth, London, Greater Lon... | 51.46040 | -0.12135 | 0 | 15 | 0.89859 | 0.80964 | 0.78856 | 0.84335 |
| 4 | Brent | London Borough of Brent, London, Greater Londo... | 51.56383 | -0.27576 | 1 | 1 | 0.99581 | 0.94416 | 0.49357 | 0.83223 |
| 32 | Westminster | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 18 | 0.84819 | 0.87310 | 0.72429 | 0.81725 |
| 8 | Ealing | London Borough of Ealing, London, Greater Lond... | 51.52508 | -0.31429 | 1 | 2 | 0.99347 | 0.96193 | 0.40681 | 0.80959 |
| 23 | Merton | London Borough of Merton, London, Greater Lond... | 51.41087 | -0.18810 | 3 | 0 | 1.00000 | 0.99492 | 0.35540 | 0.80535 |
| 30 | Waltham Forest | London Borough of Waltham Forest, London, Grea... | 51.59817 | -0.01784 | 2 | 5 | 0.97665 | 0.90102 | 0.45758 | 0.80202 |
| 10 | Greenwich | Royal Borough of Greenwich, London, Greater Lo... | 51.46863 | 0.04884 | 2 | 2 | 0.99234 | 0.92259 | 0.38046 | 0.79134 |
| 1 | Barking and Dagenham | London Borough of Barking and Dagenham, London... | 51.55412 | 0.15050 | 2 | 1 | 0.99498 | 0.92640 | 0.37211 | 0.79097 |
| 25 | Redbridge | London Borough of Redbridge, London, Greater L... | 51.58637 | 0.06976 | 2 | 1 | 0.99679 | 0.94797 | 0.34640 | 0.78947 |
| 14 | Harrow | London Borough of Harrow, London, Greater Lond... | 51.59683 | -0.33732 | 1 | 0 | 1.00000 | 0.97081 | 0.32134 | 0.78910 |
| 17 | Hounslow | London Borough of Hounslow, London, Greater Lo... | 51.46173 | -0.38013 | 1 | 3 | 0.99029 | 0.96701 | 0.31491 | 0.78185 |
| 28 | Sutton | London Borough of Sutton, London, Greater Lond... | 51.35746 | -0.17363 | 3 | 2 | 0.99173 | 0.96447 | 0.29692 | 0.77647 |
| 20 | Kingston upon Thames | Royal Borough of Kingston upon Thames, London,... | 51.38178 | -0.27699 | 3 | 4 | 0.98054 | 0.97462 | 0.30270 | 0.77571 |
| 7 | Croydon | London Borough of Croydon, London, Greater Lon... | 51.35506 | -0.06431 | 3 | 1 | 0.99790 | 0.95558 | 0.28728 | 0.77414 |
| 2 | Barnet | London Borough of Barnet, London, Greater Lond... | 51.61252 | -0.21144 | 1 | 0 | 1.00000 | 0.93655 | 0.28856 | 0.77071 |
| 9 | Enfield | London Borough of Enfield, London, Greater Lon... | 51.64874 | -0.08098 | 2 | 2 | 0.99552 | 0.94162 | 0.26478 | 0.76282 |
| 3 | Bexley | London Borough of Bexley, London, Greater Lond... | 51.46197 | 0.14570 | 2 | 0 | 1.00000 | 0.93274 | 0.25900 | 0.76088 |
| 26 | Richmond upon Thames | London Borough of Richmond upon Thames, London... | 51.44055 | -0.30764 | 1 | 5 | 0.98421 | 1.00000 | 0.22108 | 0.75922 |
| 5 | Bromley | London Borough of Bromley, London, Greater Lon... | 51.36686 | 0.06171 | 2 | 2 | 0.99759 | 0.99746 | 0.14010 | 0.74031 |
| 16 | Hillingdon | London Borough of Hillingdon, London, Greater ... | 51.54252 | -0.44833 | 1 | 0 | 1.00000 | 0.95178 | 0.16710 | 0.73807 |
| 15 | Havering | London Borough of Havering, London, Greater Lo... | 51.55793 | 0.24981 | 2 | 1 | 0.99839 | 0.95558 | 0.14524 | 0.73174 |
| 0 | City of London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 16 | 0.00000 | 0.81599 | 0.19473 | 0.26242 |
London = nominatim.query('London, Greater London, England, United Kingdom').toJSON()
print('\nCenter map on: {}\n'.format(London[0]['display_name']))
map_london = fo.Map(location=[float(London[0]['lat']), float(London[0]['lon'])], tiles='OpenStreetMap')
map_london.fit_bounds(bounds=[
[float(London[0]['boundingbox'][0]), float(London[0]['boundingbox'][2])],
[float(London[0]['boundingbox'][1]), float(London[0]['boundingbox'][3])],
])
stfun1 = lambda x: {'fillColor': 'red' if x['properties']['area']=='Inner London' else 'blue', 'fillOpacity': 0.2, 'color': 'black', 'weight': 1}
borough_map = fo.features.GeoJson(LondonBoundaries, style_function=stfun1, name='London Boroughs', tooltip=fo.GeoJsonTooltip(fields=['name'], aliases=['Borough: ']), show=False).add_to(map_london)
def stfun2(feature):
dict_out = {'fillOpacity': 0.8, 'color': 'black', 'weight': 1}
if feature['properties']['cluster']==0:
dict_out['fillColor'] = 'red'
elif feature['properties']['cluster']==1:
dict_out['fillColor'] = 'blue'
elif feature['properties']['cluster']==2:
dict_out['fillColor'] = 'lightblue'
elif feature['properties']['cluster']==3:
dict_out['fillColor'] = 'cadetblue'
return dict_out
cluster_map = fo.features.GeoJson(LondonBoundaries, style_function=stfun2, name='Cluster Boroughs', tooltip=fo.GeoJsonTooltip(fields=['name', 'cluster'], aliases=['Borough: ', 'Cluster #']), show=False).add_to(map_london)
Area_Appeal_Index = fo.Choropleth(
geo_data=LondonBoundaries,
data=DataResume,
columns=['Area_name', 'Area_Appeal_Index'],
key_on="feature.properties.name",
fill_color='RdYlBu',
fill_opacity=0.45,
line_weight=2.0,
line_opacity=0.80,
highlight=True,
name='Area_Appeal_Index',
legend_name='Area Appeal Index',
overlay=True,
show=True,
).add_to(map_london)
fo.LayerControl(overlay=True).add_to(map_london)
map_london
Center map on: London, Greater London, England, United Kingdom
Generally, the INNER LONDON Boroughs look like more appealing than OUTER LONDON ones, one relevant exception is the City of London Borough with a very poor index due to the large number of shops in a very small area.\ The best area to place the shops - fullfilling requirements - are:
WinningAreas = DataResume.groupby(['ClusterArea'], sort=True)
Winners = []
for i in ExtendedLondonDataset['ClusterArea'].unique():
print('\nCluster #{} Ranking:\n'.format(i))
display(WinningAreas.get_group(i))
Winners.append(WinningAreas.get_group(i).iloc[0]['Area_name'])
Cluster #0 Ranking:
| Area_name | Extended_name | Lat | Lon | ClusterArea | NoOfShops | Shop_Density_per_Hectar | Business_Survival | Population_Density | Area_Appeal_Index | |
|---|---|---|---|---|---|---|---|---|---|---|
| 11 | Hackney | London Borough of Hackney, London, Greater Lon... | 51.54888 | -0.04767 | 0 | 5 | 0.95243 | 0.97462 | 0.92545 | 0.94988 |
| 18 | Islington | London Borough of Islington, London, Greater L... | 51.54703 | -0.10166 | 0 | 6 | 0.92682 | 0.92005 | 1.00000 | 0.94708 |
| 29 | Tower Hamlets | London Borough of Tower Hamlets, London, Great... | 51.51456 | -0.03501 | 0 | 5 | 0.95418 | 0.88452 | 0.98779 | 0.94685 |
| 19 | Kensington and Chelsea | Royal Borough of Kensington and Chelsea, Londo... | 51.50380 | -0.20079 | 0 | 6 | 0.91027 | 0.94543 | 0.84254 | 0.89874 |
| 6 | Camden | London Borough of Camden, London, Greater Lond... | 51.54285 | -0.16251 | 0 | 3 | 0.97505 | 0.93401 | 0.71530 | 0.88686 |
| 12 | Hammersmith and Fulham | London Borough of Hammersmith and Fulham, Lond... | 51.49831 | -0.22788 | 0 | 3 | 0.96684 | 0.93147 | 0.72622 | 0.88581 |
| 31 | Wandsworth | London Borough of Wandsworth, London, Greater ... | 51.45190 | -0.19951 | 0 | 0 | 1.00000 | 0.96193 | 0.60219 | 0.87114 |
| 27 | Southwark | London Borough of Southwark, London, Greater L... | 51.46528 | -0.06904 | 0 | 12 | 0.92464 | 0.93147 | 0.69987 | 0.85892 |
| 13 | Haringey | London Borough of Haringey, London, Greater Lo... | 51.58793 | -0.10541 | 0 | 5 | 0.96938 | 0.94416 | 0.60347 | 0.85330 |
| 24 | Newham | London Borough of Newham, London, Greater Lond... | 51.53000 | 0.02932 | 0 | 1 | 0.99499 | 0.88832 | 0.60861 | 0.85241 |
| 22 | Lewisham | London Borough of Lewisham, London, Greater Lo... | 51.45343 | -0.01251 | 0 | 2 | 0.98969 | 0.93147 | 0.55463 | 0.84462 |
| 21 | Lambeth | London Borough of Lambeth, London, Greater Lon... | 51.46040 | -0.12135 | 0 | 15 | 0.89859 | 0.80964 | 0.78856 | 0.84335 |
| 32 | Westminster | City of Westminster, London, Greater London, E... | 51.49732 | -0.13715 | 0 | 18 | 0.84819 | 0.87310 | 0.72429 | 0.81725 |
| 0 | City of London | City of London, Greater London, England, Unite... | 51.51562 | -0.09200 | 0 | 16 | 0.00000 | 0.81599 | 0.19473 | 0.26242 |
Cluster #2 Ranking:
| Area_name | Extended_name | Lat | Lon | ClusterArea | NoOfShops | Shop_Density_per_Hectar | Business_Survival | Population_Density | Area_Appeal_Index | |
|---|---|---|---|---|---|---|---|---|---|---|
| 30 | Waltham Forest | London Borough of Waltham Forest, London, Grea... | 51.59817 | -0.01784 | 2 | 5 | 0.97665 | 0.90102 | 0.45758 | 0.80202 |
| 10 | Greenwich | Royal Borough of Greenwich, London, Greater Lo... | 51.46863 | 0.04884 | 2 | 2 | 0.99234 | 0.92259 | 0.38046 | 0.79134 |
| 1 | Barking and Dagenham | London Borough of Barking and Dagenham, London... | 51.55412 | 0.15050 | 2 | 1 | 0.99498 | 0.92640 | 0.37211 | 0.79097 |
| 25 | Redbridge | London Borough of Redbridge, London, Greater L... | 51.58637 | 0.06976 | 2 | 1 | 0.99679 | 0.94797 | 0.34640 | 0.78947 |
| 9 | Enfield | London Borough of Enfield, London, Greater Lon... | 51.64874 | -0.08098 | 2 | 2 | 0.99552 | 0.94162 | 0.26478 | 0.76282 |
| 3 | Bexley | London Borough of Bexley, London, Greater Lond... | 51.46197 | 0.14570 | 2 | 0 | 1.00000 | 0.93274 | 0.25900 | 0.76088 |
| 5 | Bromley | London Borough of Bromley, London, Greater Lon... | 51.36686 | 0.06171 | 2 | 2 | 0.99759 | 0.99746 | 0.14010 | 0.74031 |
| 15 | Havering | London Borough of Havering, London, Greater Lo... | 51.55793 | 0.24981 | 2 | 1 | 0.99839 | 0.95558 | 0.14524 | 0.73174 |
Cluster #1 Ranking:
| Area_name | Extended_name | Lat | Lon | ClusterArea | NoOfShops | Shop_Density_per_Hectar | Business_Survival | Population_Density | Area_Appeal_Index | |
|---|---|---|---|---|---|---|---|---|---|---|
| 4 | Brent | London Borough of Brent, London, Greater Londo... | 51.56383 | -0.27576 | 1 | 1 | 0.99581 | 0.94416 | 0.49357 | 0.83223 |
| 8 | Ealing | London Borough of Ealing, London, Greater Lond... | 51.52508 | -0.31429 | 1 | 2 | 0.99347 | 0.96193 | 0.40681 | 0.80959 |
| 14 | Harrow | London Borough of Harrow, London, Greater Lond... | 51.59683 | -0.33732 | 1 | 0 | 1.00000 | 0.97081 | 0.32134 | 0.78910 |
| 17 | Hounslow | London Borough of Hounslow, London, Greater Lo... | 51.46173 | -0.38013 | 1 | 3 | 0.99029 | 0.96701 | 0.31491 | 0.78185 |
| 2 | Barnet | London Borough of Barnet, London, Greater Lond... | 51.61252 | -0.21144 | 1 | 0 | 1.00000 | 0.93655 | 0.28856 | 0.77071 |
| 26 | Richmond upon Thames | London Borough of Richmond upon Thames, London... | 51.44055 | -0.30764 | 1 | 5 | 0.98421 | 1.00000 | 0.22108 | 0.75922 |
| 16 | Hillingdon | London Borough of Hillingdon, London, Greater ... | 51.54252 | -0.44833 | 1 | 0 | 1.00000 | 0.95178 | 0.16710 | 0.73807 |
Cluster #3 Ranking:
| Area_name | Extended_name | Lat | Lon | ClusterArea | NoOfShops | Shop_Density_per_Hectar | Business_Survival | Population_Density | Area_Appeal_Index | |
|---|---|---|---|---|---|---|---|---|---|---|
| 23 | Merton | London Borough of Merton, London, Greater Lond... | 51.41087 | -0.18810 | 3 | 0 | 1.00000 | 0.99492 | 0.35540 | 0.80535 |
| 28 | Sutton | London Borough of Sutton, London, Greater Lond... | 51.35746 | -0.17363 | 3 | 2 | 0.99173 | 0.96447 | 0.29692 | 0.77647 |
| 20 | Kingston upon Thames | Royal Borough of Kingston upon Thames, London,... | 51.38178 | -0.27699 | 3 | 4 | 0.98054 | 0.97462 | 0.30270 | 0.77571 |
| 7 | Croydon | London Borough of Croydon, London, Greater Lon... | 51.35506 | -0.06431 | 3 | 1 | 0.99790 | 0.95558 | 0.28728 | 0.77414 |